﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using SqlQueryAnalyzer.Models;
namespace SqlQueryAnalyzer
{
    public static class SqlStructQuery
    {
        
        public static List<string> GetDatabases()
        {
            string sql = "select name from sys.databases where name not in ('master','model','msdb','tempdb')";
            DataTable dt = DbHelperSQL.GetDataTable(Util.ConnectionString, sql);
            return dt.Rows.Cast<DataRow>().Select(row => row["name"].ToString()).ToList();
        }

        public static List<Table> GetTables(string database)
        {
            string sql = string.Format(@"SELECT
                                        obj.name tablename,
                                        schem.name schemname,
                                        idx.rows                                        
                                        from {0}.sys.objects obj 
                                        inner join {0}.dbo.sysindexes idx on obj.object_id=idx.id and idx.indid<=1
                                        INNER JOIN {0}.sys.schemas schem ON obj.schema_id=schem.schema_id
                                        where type='U' 
                                        order by obj.name", database);
            DataTable dt = DbHelperSQL.GetDataTable(Util.ConnectionString, sql);
            return dt.Rows.Cast<DataRow>().Select(row => new Table
            {
                TableName = row.Field<string>("tablename"),
                SchemaName = row.Field<string>("schemname"),
                Rows = row.Field<int>("rows")
            }).ToList();
        }

        public static DataTable GetColumns(string database, string tableName)
        {
            string sql = string.Format(@"select
                                        colm.name ColumnName,
                                        systype.name ColumnType,
                                        colm.is_identity IsIdentity,
                                        colm.is_nullable IsNullable,
                                        cast(colm.max_length as int) ByteLength,
                                        (
                                            case 
                                                when systype.name='nvarchar' and colm.max_length>0 then colm.max_length/2 
                                                when systype.name='nchar' and colm.max_length>0 then colm.max_length/2
                                                when systype.name='ntext' and colm.max_length>0 then colm.max_length/2 
                                                else colm.max_length
                                            end
                                        ) CharLength,
                                        cast(colm.precision as int) Precision,
                                        cast(colm.scale as int) Scale,
                                        prop.value Remark
                                        from {0}.sys.columns colm
                                        inner join {0}.sys.types systype on colm.system_type_id=systype.system_type_id and colm.user_type_id=systype.user_type_id
                                        left join {0}.sys.extended_properties prop on colm.object_id=prop.major_id and colm.column_id=prop.minor_id
                                        where colm.object_id=OBJECT_ID(@tableName)
                                        order by colm.column_id", database);
            SqlParameter param = new SqlParameter("@tableName", SqlDbType.NVarChar, 100) { Value = database + "." + tableName };
            DataTable dt = DbHelperSQL.GetDataTable(Util.ConnectionString, sql, param);
            return dt;
        }

        public static DataTable GetIndexs(string database, string tableName)
        {
            #region SQL
            string sql = string.Format(@"
                                        select 
                                        idx.name IndexName
                                        ,idx.type_desc IndexType
                                        ,idx.is_primary_key IsPrimaryKey
                                        ,idx.is_unique IsUnique
                                        ,idx.is_unique_constraint IsUniqueConstraint
                                        ,STUFF(
                                        (
	                                        SELECT  ','+c.name from {0}.sys.index_columns ic
	                                        inner join {0}.sys.columns c on ic.column_id=c.column_id and ic.object_id=c.object_id
	                                        WHERE ic.is_included_column = 0 and ic.index_id=idx.index_id AND ic.object_id=idx.object_id
	                                        ORDER BY ic.key_ordinal
	                                        FOR XML PATH('')
                                        ),1,1,'') IndexColumns
                                        ,STUFF(
                                        (
	                                        SELECT  ','+c.name from {0}.sys.index_columns ic
	                                        inner join {0}.sys.columns c on ic.column_id=c.column_id and ic.object_id=c.object_id
	                                        WHERE ic.is_included_column = 1 and ic.index_id=idx.index_id AND ic.object_id=idx.object_id
	                                        ORDER BY ic.key_ordinal
	                                        FOR XML PATH('')
                                        ),1,1,'') IncludeColumns
                                        from {0}.sys.indexes idx
                                        where object_id =OBJECT_ID(@tableName)", database);
            #endregion
            SqlParameter param = new SqlParameter("@tableName", SqlDbType.NVarChar, 100) { Value = database + "." + tableName };
            return DbHelperSQL.GetDataTable(Util.ConnectionString, sql, param);
        }
    }
}
